﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace doanqlhs.DATA
{
    class OleDB
    {
        #region Biến_toàn_cục        
        //Tạo kết nối CSDL
        public static string g_ConnectionString;
        public static OleDbConnection g_connection;
        
        //Chạy câu lệnh SQL
        public static string g_Sql;
        public static OleDbCommand g_command;

        //Đối tượng đọc dữ liệu trả về      
        public static OleDbDataReader g_reader;
        #endregion


        /*  InitOleDB : Khởi tạo CSDL
         *  Xuất ra thông báo lỗi
         */
        #region InitOleDB
        public static int InitOleDB()
        {
            g_ConnectionString = "Provider = Microsoft.Jet.OleDb.4.0; Data Source = Database.mdb";
            g_connection = new OleDbConnection(g_ConnectionString);
            try
            {
                g_connection.Open();
            }
            catch (OleDbException e)
            {
                return -1;
            }
            return 0;
        }
        #endregion

        public static void close ()
        {
            g_connection.Close();
        }
        /*  Insert : Đưa thông tin từ list _in_component vào CSDL
         *  Trả về 0 nếu thỏa mọi quy định, ngược lại ≠ 0
         */
        #region Insert
        public static int Insert(string _in_TableName, List<string> _in_component)
        {
            switch (_in_TableName) {
                //tên các bảng trong cơ sở dữ liệu
                case "SV":
                    {
                        #region Insert_SV
                        g_Sql = "insert into SV(MASV,HOTEN,NGAYSINH,GIOITINH,DIACHI,MALOP) values('" + _in_component[0] + "','" + _in_component[1] + "','" + _in_component[2] + "','" + _in_component[3] + "','" + _in_component[4] + "','" + _in_component[5] + "')";
                        try
                        {
                            g_command = new OleDbCommand(g_Sql, g_connection);
                            g_command.ExecuteNonQuery();
                        }
                        catch (OleDbException ex)
                        {
                            return -1;
                        }
                        #endregion
                    }
                    return 0;
                case "KHOILOP":
                    {
                        #region Insert_KhoiLop
                        g_Sql = "insert into KHOILOP(MAKL,TENKL,SOLOPTOIDA) values('" + _in_component[0] + "','" + _in_component[1] + "'," + _in_component[2] + ")";
                        try
                        {
                            g_command = new OleDbCommand(g_Sql, g_connection);
                            g_command.ExecuteNonQuery();
                        }
                        catch (OleDbException ex)
                        {
                            return -1;
                        }
                        #endregion
                    }
                    return 0;
                case "LOP":
                    {
                        #region  Insert_Lop
                        g_Sql = "insert into LOP(MALOP,TENLOP,SISO,MAKL) values('" + _in_component[0] + "','" + _in_component[1] + "'," + _in_component[3] + "')";
                        try
                        {
                            g_command = new OleDbCommand(g_Sql, g_connection);
                            g_command.ExecuteNonQuery();
                        }
                        catch (OleDbException ex)
                        {
                            return -1;
                        }
                        #endregion
                    }
                    return 0;

                case "MONHOC":
                    {
                        #region Insert_MonHoc
                        g_Sql = "insert into MONHOC(MAMH,MONHOC) values('" + _in_component[0] + "','" + _in_component[1] + "')";
                        try
                        {
                            g_command = new OleDbCommand(g_Sql, g_connection);
                            g_command.ExecuteNonQuery();
                        }
                        catch (OleDbException ex)
                        {
                            return -1;
                        }
                        #endregion
                    }
                    return 0;
                case "BANGDIEM":
                    {
                        #region Insert_BangDiem
                        #region Insert_MonHoc
                        g_Sql = "insert into BANGDIEM(MAHS,MAHK,MAMH,DIEM15,DIEM1T,DIEMCUOIHK) values('" + _in_component[0] + "','" + _in_component[1] + "','" + _in_component[2] + "'," + _in_component[3] + "," + _in_component[4] + "," + _in_component[5] + ")";
                        try
                        {
                            g_command = new OleDbCommand(g_Sql, g_connection);
                            g_command.ExecuteNonQuery();
                        }
                        catch (OleDbException ex)
                        {
                            return -1;
                        }
                        #endregion
                        #endregion
                    }
                    return 0;

                default:
                    return -1;
            }
        }
        #endregion

        /*  Update : Chỉnh sửa thông tin từ list _in_component vào CSDL
         *  Trả về 0 nếu thỏa mọi quy định, ngược lại ≠ 0
         */
        #region Update
        public static int Update(string _in_TableName, List<string> _in_component)
        {
            
            switch (_in_TableName)
            {
                //tên các bảng trong cơ sở dữ liệu
                case "LOP":
                    {
                        #region  Update_Lop
                        g_Sql = "Update LOP set TENLOP = '" + _in_component[1]
                                        + "', SISO = "      + _in_component[2] + " "
                                        + "where MALOP = '" + _in_component[0] + "'" ;
                       
                        try
                        {
                            g_command = new OleDbCommand(g_Sql, g_connection);
                            g_command.ExecuteNonQuery();
                        }
                        catch (OleDbException ex)
                        {
                            return -1;
                        }
                        #endregion
                    }
                    return 0;

                case "MONHOC":
                    {
                        #region Update_MonHoc
                        g_Sql = "Update MONHOC set MONHOC = '" + _in_component[1] + "' "
                                        + "where MAMH = '" + _in_component[0] + "'";
                        try
                        {
                            g_command = new OleDbCommand(g_Sql, g_connection);
                            g_command.ExecuteNonQuery();
                        }
                        catch (OleDbException ex)
                        {
                            return -1;
                        }
                        #endregion
                    }
                    return 0;
                case "THAMSO":
                    {
                        #region Update_BangDiem

                        #endregion
                    }
                    return 0;

                default:
                    return -1;
            }
        }
        #endregion
    }
}
